﻿using OfficeOpenXml;
using PhuLeBussinessObject.CProductType;
using PhuLeDao.Product;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;

namespace PhuLeBooze.UICode
{
    public class CProductUI
    {
        public string ImportExcel(string filePath)
        {
            string Result = "";
            DataSet dataset = new DataSet();
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(filePath))
                {
                    pck.Load(stream);
                }

                String wSheet = "";
              
                foreach (var ws1 in pck.Workbook.Worksheets)
                {
                    DataTable dt = ReadExcelSheet(ws1);
                    dataset.Tables.Add(dt);
                }
                
            }
            return Result;

        }
        private DataTable ReadExcelSheet(ExcelWorksheet sheet)
        {
            try
            {
                DataTable tbl = new DataTable(sheet.Name);
                bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
                foreach (var firstRowCell in sheet.Cells[1, 1, 1, sheet.Dimension.End.Column])
                {
                    tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }
                var startRow = hasHeader ? 2 : 1;
                for (var rowNum = startRow; rowNum <= sheet.Dimension.End.Row; rowNum++)
                {
                    var wsRow = sheet.Cells[rowNum, 1, rowNum, sheet.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }
                return tbl;
            }
            catch (Exception e)
            {
                throw new Exception("Can not read excel sheet: " + sheet.Name);
            }
        }

        public string GetProductFilter(string pCode) {

            CProductDao productDao = new CProductDao();
            IList<CProduct> listProduct = productDao.GetProductFilter(string.Format("<InputValue Code=\"{0}\"/>", pCode));
             JavaScriptSerializer js = new JavaScriptSerializer();
            return js.Serialize(listProduct);
        }

    }
}